package com.hcss.common.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * http://zc985552943.iteye.com/blog/1491546
 */
public class ExcelUtil {
	
	/**
	 * 默认解析Excel文件数据信息
	 * @param file
	 * @param col2AttrMap
	 * @param claz
	 * @return
	 * @throws Exception
	 */
	public static<T> List<T> parse(InputStream is, Map<String, String> col2AttrMap, Class<T> claz) 
			throws Exception{
		if(is == null){
			return null;
		}
		List<T> result = new ArrayList<T>();
		try {
			Workbook workbook = WorkbookFactory.create(is);
			
			Sheet sheet = workbook.getSheetAt(0);
			Integer maxRow = sheet.getPhysicalNumberOfRows();
			if(maxRow < 2){
				return null;
			}
			
			// 根据列名和属性名称的映射关系获取列index和属性名的映射关系
			Map<String, String> colIndex2AttrMap = new HashMap<String, String>();
			Row header = sheet.getRow(0);
			Integer endColNumber = header.getPhysicalNumberOfCells();
			for (int j = 0; j < endColNumber; j++) {
				String value = getCellValue(header.getCell(j));
				colIndex2AttrMap.put(String.valueOf(j), col2AttrMap.get(value));
			}
			
			// 解析剩余Excel记录成数据
			for (int i = 1; i < maxRow; i++) {
				Row row = sheet.getRow(i);
				endColNumber = row.getPhysicalNumberOfCells();
				T object = claz.newInstance();
				for (int j = 0; j < endColNumber; j++) {
					String value = getCellValue(row.getCell(j));
					ReflectUtil.setValueByString(object, colIndex2AttrMap.get(String.valueOf(j)), value);
				}
				result.add(object);
			}
			
			return result;
		} finally {
			StreamUtil.close(is);
		}
	}
	
	/**
	 * 默认解析Excel文件数据信息
	 * @param is 文件流
	 * @param cloumnNames 字段名数组
	 * @param claz 返回类型
	 * @return
	 * @throws Exception
	 */
	public static<T> List<T> parse(InputStream is, String[] cloumnNames, Class<T> claz) 
			throws Exception{
		if(is == null){
			return null;
		}
		List<T> result = new ArrayList<T>();
		try {
			Workbook workbook = WorkbookFactory.create(is);
			
			Sheet sheet = workbook.getSheetAt(0);
			Integer maxRow = sheet.getPhysicalNumberOfRows();
			Integer lastRowNum = sheet.getLastRowNum();
			if(maxRow < 2){
				return null;
			}
			
			// 根据列名和属性名称的映射关系获取列index和属性名的映射关系
			Row header = sheet.getRow(0);
			Integer endColNumber = header.getPhysicalNumberOfCells();
			// 取excel列数与传入字段数较小值，记为需要取值的列数
			endColNumber = endColNumber < cloumnNames.length ? endColNumber : cloumnNames.length;
			
			// 解析剩余Excel记录成数据
			for (int i = 1; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				// 如果 row 为空，跳过
				if (row == null) {
					continue;
				}
				T object = claz.newInstance();
				boolean allColIsNull = true;//所有列值都为控
				for (int j = 0; j < endColNumber; j++) {
					Cell cell = row.getCell(j);//如果cell为空，就不需要赋值
					if (cell != null) {
						String value = getCellValue(cell);
						ReflectUtil.setValueByString(object, cloumnNames[j], value);
						allColIsNull = false;
					}
				}
				if (!allColIsNull){ // 如果所有列值都为空，object也一样为空，不算一条记录
					result.add(object);
				}
			}
			
			return result;
		} finally {
			StreamUtil.close(is);
		}
	}
	
	/**
	 * excel导入
	 * @param c 导入Excel对应的model类
	 * @param file 导入的文件
	 * @param map 当前导入模版类型的表头名称映射
	 * @return
	 */
	public static List<Map<String, Object>> excelImport(InputStream is, Map<String, Object> map) {
		// 根据excel类型获取映射的Map
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			// 获取Excel对象 2003 2007均可
			Workbook workbook = WorkbookFactory.create(is);
			// 读取第一章表格内容
			Sheet sheet = workbook.getSheetAt(0);
			if (sheet != null) {
			
				for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
					if (sheet.getLastRowNum() < i) {
						break;
					}
					Map<String, Object> columns = new HashMap<String, Object>();
					// 获取第一行的数据
					Row head = sheet.getRow(sheet.getFirstRowNum() == 0 ? sheet.getFirstRowNum()
							: sheet.getFirstRowNum() - 1);
					// 获取每行数据
					Row row = sheet.getRow(i);
					// 循环一行的每列数据
					for (int j = head.getFirstCellNum(); j <= head
							.getPhysicalNumberOfCells(); j++) {

						// 判断Excel表头的映射
						String columnName = (String) map
								.get(head.getCell(j) == null ? "" : head
										.getCell(j).toString());
						// 如果映射不存在跳出当前循环 进入下一次循环s
						if (columnName == null) {
							continue;
						}
						Cell cell = row.getCell(j);
						String value = isCellType(cell);
						columns.put(columnName, value);
						// 获取值
					}
					if (columns != null && columns.size() > 0) {
						list.add(columns);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 导出到某个输出流上
	 * 
	 * @param list
	 *            存储的值
	 * @param field2Label
	 *            对象域和标签的映射关系
	 * @param outputStream
	 *            输出流
	 * @param sheetName
	 *            sheet名称
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static void exportExcel(List<?> list, Map<String, String> field2Label, OutputStream outputStream,
			String sheetName) throws Exception {
		XSSFWorkbook workBook = new XSSFWorkbook();

		XSSFSheet sheet = workBook.createSheet(sheetName);
		XSSFCell cell = null;

		if (list == null || list.size() == 0) {
			return;
		}
		int columnCount = 0;
		Set<Entry<String, String>> flEntry = field2Label.entrySet();
		XSSFRow headerRow = sheet.createRow(0);
		for (Entry<String, String> one : flEntry) {// 添加header
			cell = headerRow.createCell(columnCount++);
			cell.setCellValue(one.getValue());
		}

		for (int j = 0; j < list.size(); j++) {// 添加body
			XSSFRow row = sheet.createRow(j + 1);
			Object obj = list.get(j);

			columnCount = 0;
			for (Entry<String, String> one : flEntry) {
				cell = row.createCell(columnCount++);
				Object temp = ReflectUtil.getFieldValue(obj, one.getKey());
				if (temp != null && !"".equals(temp)) {
					cell.setCellValue(temp.toString());
				} else {
					cell.setCellValue("");// 如果忽略这一步的话，会出现意外的问题，尤其是时间类型的数据
				}
			}
		}
		try {
			workBook.write(outputStream);
			outputStream.flush();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 判断读取的值类型
	 * 
	 * @param cell
	 * @return
	 */
	private static String isCellType(Cell cell) {
		String str = "";
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
							.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					str = sdf.format(date);
				} else {
					str = new DecimalFormat("#.#########").format(cell
							.getNumericCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				str = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				str = cell.getBooleanCellValue() + "";
				break;
			case HSSFCell.CELL_TYPE_FORMULA: // 公式
				str = cell.getCellFormula() + "";
				break;
			case HSSFCell.CELL_TYPE_BLANK: // 空值
				break;
			case HSSFCell.CELL_TYPE_ERROR: // 故障
				break;
			default:
				break;
			}
		} else {
			System.out.print("-   ");
		}
		return str;
	}
	
	/**
	 * 获取cell的值
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {
		String result = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				result = new DecimalFormat("#").format(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				result = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				result = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_FORMULA: // 公式
				result = String.valueOf(cell.getCellFormula());
				break;
			case HSSFCell.CELL_TYPE_BLANK: // 空值
			case HSSFCell.CELL_TYPE_ERROR: // 故障
			default:
				break;
		}
		return result;
	}
	
	/**
	 * 创建excel 工作簿
	 * @param list 第一个Map 元素 需要包含 sheetName（key），来指定sheet页的名称 
	 * @param keys
	 * @param columnNames
	 * @return
	 */
	public static<T> Workbook createWorkBook(List<Map<String, T>> list, String[] keys, String[] columnNames) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        HSSFCellStyle cs = (HSSFCellStyle) wb.createCellStyle();
        HSSFCellStyle cs2 = (HSSFCellStyle) wb.createCellStyle();

        // 创建两种字体
        HSSFFont f = (HSSFFont) wb.createFont();
        HSSFFont f2 = (HSSFFont) wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 11);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 11);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow( i );
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
}
